﻿//信息基表
//创建时间: 2010-12-10
//创建作者: 李城
//功能说明: CMS系统 评论模块 
//最后修改: 2010-12-10
using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;
using System.Data;
using TRPOP.Common;
using TRPOP.Config;
using TRPOP.CMS.Model;
using TRPOP.CMS.DALFactory;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace TRPOP.CMS.DALSQLServer
{
    public class RecomDAL : IRecomDAL
    {

        #region 保存 Save
        public EnumSubmitResult Save(RecomInfo info)
        {
            string strStoreProcedure = SystemInfoHelper.getSystemInfo().DBprefixTableName + "_" + "Recom_Save";
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure);

            odbDataBase.AddInParameter(odbCommand, "@Name", System.Data.DbType.String, info.Name);
            odbDataBase.AddInParameter(odbCommand, "@Detail", System.Data.DbType.String, info.Detail);
            odbDataBase.AddParameter(odbCommand, "@RecommendId", System.Data.DbType.Int32, ParameterDirection.InputOutput, null, DataRowVersion.Default, info.RecommendId);

            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                info.RecommendId = (int)odbDataBase.GetParameterValue(odbCommand, "@RecommendId");
                enmResult = EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }

        #endregion

        #region 获取实体 GetInfoById
        public RecomInfo GetInfoById(int id)
        {
            string strSqlString = "SELECT * FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_rec_Recom WHERE RecommendId=" + id;
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);
            RecomInfo oInfo = null;
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    if (reader.Read())
                    {
                        oInfo = new RecomInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                    }
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oInfo;
        }
        #endregion

        #region 删除 DeleteInfoById
        public EnumSubmitResult DeleteInfoById(int id)
        {
            return DeleteInfoByIds(id.ToString());
        }
        #endregion

        #region 删除 DeleteInfoByIds
        public EnumSubmitResult DeleteInfoByIds(string ids)
        {
            string strSqlString = "DELETE FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_rec_Recom WHERE RecommendId in(" + ids + ")";
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);

            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                enmResult = EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }
        #endregion

        #region 自定义列表（无分页） GetListByCondition
        public List<RecomInfo> GetListByCondition(int topn, string condition, string orderby)
        {
            string strSqlString = "SELECT ";
            if (topn > 0)
            {
                strSqlString += " TOP " + topn;
            }
            strSqlString += " * FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_rec_Recom WHERE 1=1"; if (condition != "")
            {
                strSqlString += condition;
            }
            if (orderby != "")
            {
                strSqlString += " ORDER BY " + orderby;
            }
            else
            {
                strSqlString += " ORDER BY RecommendId DESC";
            }

            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);
            List<RecomInfo> olstInfo = new List<RecomInfo>(); RecomInfo oInfo = null;
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    while (reader.Read())
                    {
                        oInfo = new RecomInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                        olstInfo.Add(oInfo);
                    }
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return olstInfo;
        }
        #endregion

        #region 自定义列表（分页） GetListByPager
        public List<RecomInfo> GetListByPager(int pageNumber, int pageSize, string condition, string orderby, out int recordCount)
        {
            string strStoreProcedure = SystemInfoHelper.getSystemInfo().PagerStoreProcedureName;
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure);
            List<RecomInfo> olstInfo = new List<RecomInfo>(); RecomInfo oInfo = null;
            odbDataBase.AddInParameter(odbCommand, "@TableName", System.Data.DbType.String, SystemInfoHelper.getSystemInfo().DBprefixTableName + "_rec_Recom");
            odbDataBase.AddInParameter(odbCommand, "@ShowColumns", System.Data.DbType.String, "*");
            odbDataBase.AddInParameter(odbCommand, "@pageNumber", System.Data.DbType.Int32, pageNumber);
            odbDataBase.AddInParameter(odbCommand, "@pageSize", System.Data.DbType.Int32, pageSize);
            odbDataBase.AddInParameter(odbCommand, "@condition", System.Data.DbType.String, condition);
            odbDataBase.AddInParameter(odbCommand, "@orderby", System.Data.DbType.String, orderby);
            odbDataBase.AddOutParameter(odbCommand, "@recordCount", System.Data.DbType.Int32, 0);
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    while (reader.Read())
                    {
                        oInfo = new RecomInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                        olstInfo.Add(oInfo);
                    }
                }
                recordCount = int.Parse(odbCommand.Parameters["@RecordCount"].Value.ToString());
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return olstInfo;
        }
        #endregion

        #region 新增的方法

        public DataTable GetRecommentDT(int NodeID, int intPageNum, int intPageSize, out int intRecount)
        {
            string strTableName = string.Format(" {0}_rec_RecomContent,  {0}_content_Content , {0}_rec_Recom ", SystemInfoHelper.getSystemInfo().DBprefixTableName);
            string strShowColumns = string.Format(" {0}_rec_RecomContent.*, {0}_content_Content.url as contentURL , {0}_content_Content.name as contentName,{0}_rec_Recom.name as recName ", SystemInfoHelper.getSystemInfo().DBprefixTableName);
            string strCondition = string.Format("  and {0}_rec_RecomContent.[ContentId]={0}_content_Content.ContentId and {0}_rec_RecomContent.RecomId={0}_rec_Recom.RecommendId ", SystemInfoHelper.getSystemInfo().DBprefixTableName);
            string strOrderBy = string.Format(" {0}_rec_RecomContent.AddTime desc ", SystemInfoHelper.getSystemInfo().DBprefixTableName);
            if (NodeID > 0)
            {
                strCondition += string.Format("  and {0}_content_Content.NodeId={1} ", SystemInfoHelper.getSystemInfo().DBprefixTableName, NodeID);
            }
            return (new TRPOP.ComSqlDAL.CommonDAL()).GetListByPager(strTableName, strShowColumns, intPageNum, intPageSize, strCondition, strOrderBy, out intRecount, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection).Tables[0];
        }


        public DataTable GetRecommentDT(int NodeID, int intPageNum, int intPageSize, out int intRecount, int intContentID, DateTime timeStart, DateTime timeEnd,string strSQLNodeIDsCondation)
        {
            string strTableName = string.Format(" {0}_rec_RecomContent,  {0}_content_Content , {0}_rec_Recom ", SystemInfoHelper.getSystemInfo().DBprefixTableName);
            string strShowColumns = string.Format(" {0}_rec_RecomContent.*, {0}_content_Content.url as contentURL , {0}_content_Content.name as contentName,{0}_rec_Recom.name as recName ", SystemInfoHelper.getSystemInfo().DBprefixTableName);
            string strCondition = string.Format("  and {0}_rec_RecomContent.[ContentId]={0}_content_Content.ContentId and {0}_rec_RecomContent.RecomId={0}_rec_Recom.RecommendId ", SystemInfoHelper.getSystemInfo().DBprefixTableName);
            string strOrderBy = string.Format(" {0}_rec_RecomContent.AddTime desc ", SystemInfoHelper.getSystemInfo().DBprefixTableName);
            if (NodeID > 0)
            {
                strCondition += string.Format("  and {0}_content_Content.NodeId={1} ", SystemInfoHelper.getSystemInfo().DBprefixTableName, NodeID);
            }
            if (intContentID > 0)
            {
                strCondition += string.Format("  and {0}_content_Content.ContentId={1} ", SystemInfoHelper.getSystemInfo().DBprefixTableName, intContentID);
            }
            if (timeStart != StringExtension.NullDateTime)
            {
                strCondition += string.Format("  and {0}_rec_RecomContent.AddTime >= '{1}' ", SystemInfoHelper.getSystemInfo().DBprefixTableName, timeStart.ToString());
            }
            if (timeEnd != StringExtension.NullDateTime)
            {
                strCondition += string.Format("  and {0}_rec_RecomContent.AddTime <= '{1}' ", SystemInfoHelper.getSystemInfo().DBprefixTableName, timeEnd.ToString());
            }
            if (strSQLNodeIDsCondation.IsEmpty() ==false)
            {
                strCondition += string.Format("  and {0}_content_Content.NodeId in ({1})    ", SystemInfoHelper.getSystemInfo().DBprefixTableName, strSQLNodeIDsCondation);
            }
            return (new TRPOP.ComSqlDAL.CommonDAL()).GetListByPager(strTableName, strShowColumns, intPageNum, intPageSize, strCondition, strOrderBy, out intRecount, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection).Tables[0];
        }


        public DataTable GetRecommentDT(int NodeID, int intPageNum, int intPageSize, out int intRecount, int intContentID, DateTime timeStart, DateTime timeEnd)
        {
            string strTableName = string.Format(" {0}_rec_RecomContent,  {0}_content_Content , {0}_rec_Recom ", SystemInfoHelper.getSystemInfo().DBprefixTableName);
            string strShowColumns = string.Format(" {0}_rec_RecomContent.*, {0}_content_Content.url as contentURL , {0}_content_Content.name as contentName,{0}_rec_Recom.name as recName ", SystemInfoHelper.getSystemInfo().DBprefixTableName);
            string strCondition = string.Format("  and {0}_rec_RecomContent.[ContentId]={0}_content_Content.ContentId and {0}_rec_RecomContent.RecomId={0}_rec_Recom.RecommendId ", SystemInfoHelper.getSystemInfo().DBprefixTableName);
            string strOrderBy = string.Format(" {0}_rec_RecomContent.AddTime desc ", SystemInfoHelper.getSystemInfo().DBprefixTableName);
            if (NodeID > 0)
            {
                strCondition += string.Format("  and {0}_content_Content.NodeId={1} ", SystemInfoHelper.getSystemInfo().DBprefixTableName, NodeID);
            }
            if (intContentID > 0)
            {
                strCondition += string.Format("  and {0}_content_Content.ContentId={1} ", SystemInfoHelper.getSystemInfo().DBprefixTableName, intContentID);
            }
            if (timeStart != StringExtension.NullDateTime)
            {
                strCondition += string.Format("  and {0}_rec_RecomContent.AddTime >= '{1}' ", SystemInfoHelper.getSystemInfo().DBprefixTableName, timeStart.ToString());
            }
            if (timeEnd != StringExtension.NullDateTime)
            {
                strCondition += string.Format("  and {0}_rec_RecomContent.AddTime <= '{1}' ", SystemInfoHelper.getSystemInfo().DBprefixTableName, timeEnd.ToString());
            }
            return (new TRPOP.ComSqlDAL.CommonDAL()).GetListByPager(strTableName, strShowColumns, intPageNum, intPageSize, strCondition, strOrderBy, out intRecount, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection).Tables[0];
        }

        public int GetContentRecCount(int intIndexid)
        {
            DataSet ds = new DataSet();
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = null;
            string strExeSQL = string.Format(@"
                                                select COUNT(0) 
                                                from [{0}_rec_RecomContent]
                                                where ContentId={1}
                                                group by ContentId
                                                ", SystemInfoHelper.getSystemInfo().DBprefixTableName, intIndexid);
            odbCommand = odbDataBase.GetSqlStringCommand(strExeSQL);
            try
            {
                using (ds = odbDataBase.ExecuteDataSet(odbCommand))
                {
                    return (ds.Tables[0].Rows.Count > 0 ?  ds.Tables[0].Rows[0][0].ToString().ToInt() : 0);
                }
            }
            catch(Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
        }
        public int GetNodeRecCount(int intNodeid)
        {
            DataSet ds = new DataSet();
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = null;
            string strExeSQL = string.Format(@"
                                                select COUNT(0) 
                                                from [{0}_rec_RecomContent],{0}_content_Content
                                                where [{0}_rec_RecomContent].ContentId={0}_content_Content.ContentId
                                                      and {0}_content_Content.NodeId={1}
                                                group by NodeId
                                                ", SystemInfoHelper.getSystemInfo().DBprefixTableName, intNodeid);
            odbCommand = odbDataBase.GetSqlStringCommand(strExeSQL);
            try
            {
                using (ds = odbDataBase.ExecuteDataSet(odbCommand))
                {
                    return (ds.Tables[0].Rows.Count > 0 ? ds.Tables[0].Rows[0][0].ToString().ToInt() : 0);
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
        }
        #endregion
    }
}


